package controle.acesso.dao;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import controle.acesso.model.UseCase;

import utils.Constantes;
import utils.Utils;

public class UseCaseDao extends GenericDao{
	


private int numerodecampos = 2;
	
	public UseCaseDao(){
		super();
	}


public PreparedStatement adicionar(UseCase useCase) throws SQLException{
		
		String sql = "insert into useCase(nome,descricao) values (?,?)";
		this.setQuery(sql);
		ArrayList<Integer> indexes=new ArrayList<Integer>();
		for (int i=0;i<numerodecampos;i++)
			indexes.add(i,i+1);
		indexes.add(numerodecampos,null);
		
		PreparedStatement stmt = this.loadModelToPreparedStatement(useCase,indexes);
		
		stmt.execute();	
		this.setQuery(stmt.toString());

		stmt.close();
		
		con.close();
		return stmt;
		
	}	

public PreparedStatement desativar(UseCase useCase) throws SQLException{
	
	
	String sql = "update usecase set ativo = 'N' where idusecase = ?";
	this.setQuery(sql);
	ArrayList<Integer> indexes=new ArrayList<Integer>();
	for (int i=0;i<numerodecampos;i++)
		indexes.add(i,null);
	indexes.add(numerodecampos,1);
	PreparedStatement stmt = this.loadModelToPreparedStatement(useCase, indexes);
	
	stmt.execute();
	this.setQuery(stmt.toString());

	stmt.close();
	
	con.close();
	return stmt;
	
	

}

public PreparedStatement ativar(UseCase useCase) throws SQLException{
	
	String sql = "update usecase set ativo = 'S' where idusecase = ?";
	this.setQuery(sql);
	ArrayList<Integer> indexes=new ArrayList<Integer>();
	for (int i=0;i<numerodecampos;i++)
		indexes.add(i,null);
	indexes.add(numerodecampos,1);
	PreparedStatement stmt = this.loadModelToPreparedStatement(useCase, indexes);
	
	stmt.execute();
	this.setQuery(stmt.toString());

	stmt.close();
	
	con.close();
	return stmt;
	
	

}


	public PreparedStatement apagar(UseCase useCase) throws SQLException{
	String sql = "delete from usecase where idusecase = ?";
	this.setQuery(sql);
	
	ArrayList<Integer> indexes=new ArrayList<Integer>();
	for (int i=0;i<numerodecampos;i++)
		indexes.add(i,null);
	indexes.add(numerodecampos,1);
	
	PreparedStatement stmt = this.loadModelToPreparedStatement(useCase, indexes);
	
	stmt.execute();
	this.setQuery(stmt.toString());

	stmt.close();
	
	con.close();
	return stmt;
	
	

}

public PreparedStatement alterar(UseCase useCase) throws SQLException{
	
	String sql = "update usecase set nome = ?,descricao = ?" +
			" where idusecase = ? and ativo = 'S'";
	this.setQuery(sql);
	ArrayList<Integer> indexes=new ArrayList<Integer>();
	for (int i=0;i<=numerodecampos;i++)
		indexes.add(i,i+1);
	
	
	PreparedStatement stmt =this.loadModelToPreparedStatement(useCase,indexes); 
	
	stmt.execute();
	
	this.setQuery(stmt.toString());

	stmt.close();
	
	con.close();
	return stmt;
	
}

public Integer countGetUseCases(UseCase useCaseBusca) throws SQLException{
	if (useCaseBusca==null) return null;
	String sql = "select count(*) as count from usecase where 1=1";		
	this.setQuery(sql);
	this.addAtivo(useCaseBusca.getAtivo().charAt(0));
	ArrayList<Integer> restrictions=this.prepareRestrictionsQuery(useCaseBusca);
	
	PreparedStatement stmt =this.addRestrictions(useCaseBusca, restrictions);
			
	ResultSet rs = stmt.executeQuery();
	Integer count = new Integer(0);
	
	while(rs.next()){
		if (!Utils.isBlank(rs.getInt(Constantes.COUNT)))
			count=rs.getInt(Constantes.COUNT);
	} 
	this.setQuery(stmt.toString());

	stmt.close();
	rs.close();
	con.close();
	
	return count;
}




public ArrayList<UseCase> getUseCases(UseCase useCaseBusca, int limite, int inicio) throws SQLException{
	if (useCaseBusca==null) return null;
	String sql = "select * from usecase where 1=1";		
	this.setQuery(sql);
	this.addAtivo(useCaseBusca.getAtivo().charAt(0));
	ArrayList<Integer> restrictions=this.prepareRestrictionsQuery(useCaseBusca);
	this.addOrder("asc","nome");
	this.addLimit(limite,inicio);
	
	PreparedStatement stmt =this.addRestrictions(useCaseBusca, restrictions);
			
	ResultSet rs = stmt.executeQuery();
	UseCase useCase = null;
	ArrayList<UseCase> useCases=new ArrayList<UseCase>();
	
	while(rs.next()){
		useCase = this.loadResultSetToModel(rs);
		useCases.add(useCase);
		
	} 
	this.setQuery(stmt.toString());

	stmt.close();
	rs.close();
	con.close();
	
	return useCases;
}





private PreparedStatement addRestrictions(UseCase useCase,
		ArrayList<Integer> restrictions) throws SQLException {
PreparedStatement stmt=con.prepareStatement(this.getQuery());
	
	if (!Utils.isBlank(restrictions.get(0)))
		stmt.setString(restrictions.get(0), "%"+useCase.getNome()+"%");
	
	if (!Utils.isBlank(restrictions.get(1)))
		stmt.setString(restrictions.get(1), "%"+useCase.getDescricao()+"%");
	
	if (!Utils.isBlank(restrictions.get(2)))
		stmt.setInt(restrictions.get(2), useCase.getId());
	return stmt;
}





private PreparedStatement loadModelToPreparedStatement(UseCase useCase ,ArrayList<Integer> indexes) throws SQLException{
	PreparedStatement stmt=con.prepareStatement(this.getQuery());
	
	if (!Utils.isBlank(indexes.get(0)))
		stmt.setString(indexes.get(0), useCase.getNome());
	
	if (!Utils.isBlank(indexes.get(1)))
		stmt.setString(indexes.get(1), useCase.getDescricao());
	
	if (!Utils.isBlank(indexes.get(2)))
		stmt.setInt(indexes.get(2), useCase.getId());
	
	return stmt;
}


private UseCase loadResultSetToModel(ResultSet rs) throws SQLException {
	UseCase useCase = new UseCase();
	
	if (!Utils.isBlank(rs.getInt(Constantes.IDUSECASE)))
		useCase.setId(rs.getInt(Constantes.IDUSECASE));
	
	if (!Utils.isBlank(rs.getString(Constantes.NOME)))
		useCase.setNome(rs.getString(Constantes.NOME));
	
	if (!Utils.isBlank(rs.getString(Constantes.DESCRICAO)))
		useCase.setDescricao(rs.getString(Constantes.DESCRICAO));
	
	if (!Utils.isBlank(rs.getString(Constantes.ATIVO)))
		useCase.setAtivo(rs.getString(Constantes.ATIVO));
	
	return useCase;
}





private ArrayList<Integer> prepareRestrictionsQuery(UseCase useCase){
	ArrayList<Integer> restrictions=new ArrayList<Integer>();
	int index=0;
	String sql=this.getQuery();
	
	restrictions.add(0, null);
	if (!Utils.isBlank(useCase.getNome())){
		sql+=" and nome like ?";
		index++;
		restrictions.add(0, index);
	}
	
	
	
	restrictions.add(1, null);
	if (!Utils.isBlank(useCase.getDescricao())){
		sql+=" and descricao like ?";
		index++;
		restrictions.add(1, index);
	}
	
	restrictions.add(2, null);
	if (!Utils.isBlank(useCase.getId())){
		sql+=" and idusecase = ?";
		index++;
		restrictions.add(2, index);
	}

	
	this.setQuery(sql);
	return restrictions;
}

}
